import pymysql


# 定义数据库 工具类
class DBTools(object):
    # 建立连接 - 静态方法 - 私有
    @staticmethod
    def __create_conn():
        # 指定 ip、port、用户名、密码、数据库，返回连接
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2022", database="ihrm", charset="utf8")
        return conn

    # 查询 方法
    @staticmethod
    def query_one(sql):
        conn = None
        cursor = None
        res = None
        try:
            # 获取连接，创建游标，执行SQL，提结果集，返回一条结果
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print("查询sql失败：", err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_many(sql, n):
        cursor = None
        conn = None
        res = None
        try:
            # 获取连接，创建游标，执行SQL，提结果集，返回多条结果
            conn = DBTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(n)  # 借助形参，让用户指定查询的跳数
        except Exception as err:
            print("查询多条sql失败：", err)
        finally:
            cursor.close()
            conn.close()
            return res

    def query_all(self, sql):
        pass
        # 获取连接，创建游标，执行SQL，提结果集，返回全部结果

    # 增删改 方法
    @staticmethod
    def db_idu(sql):
        conn = None
        cursor = None
        try:
            # 创建连接
            conn = DBTools.__create_conn()
            # 创建游标
            cursor = conn.cursor()
            # 执行 sql语句。
            ret = cursor.execute(sql)
            print("Affected rows:", ret)
            # 执行成功，提交事务。 commit()
            conn.commit()
        except Exception as err:
            # 回滚事务
            conn.rollback()
            print("增删改SQL失败：", err)
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()


if __name__ == '__main__':
    # res = DBTools.query_one("select * from t_book;")
    # print("查一条：", res)
    #
    # res2 = DBTools.query_many("select * from t_book;", 3)
    # print("查3条：", res2)
    #
    # # 测试 insert
    # DBTools.db_idu("insert into t_book(id, title, pub_date) VALUES(118, '西游记', '1986-01-01');")
    #
    # # 测试 update
    # DBTools.db_idu("update t_book set `read` = `read` + 1 where title = '西游记';")
    sql = "insert into bs_user(id, mobile, username) values('787871234', '18947382464', 'jack');"
    DBTools.db_idu(sql)
